package cn.kami.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.kami.dao.IBatteryDAO;
import cn.kami.vo.Battery;

public class BatteryDAOImpl implements IBatteryDAO {
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	public BatteryDAOImpl(Connection conn){
		this.conn = conn;
	}
	@Override
	public boolean isBattery(String batID) throws Exception {
		// TODO Auto-generated method stub
		boolean flag = false;
		String sql = "SELECT bat_id FROM Battery WHERE bat_id=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,batID);
		ResultSet rs = this.pstmt.executeQuery();
		if(rs.next()){
			flag = true;
		}
		this.pstmt.close();
		return flag;
	}
	@Override
	public boolean UpBattery(Battery bat) throws Exception {
		// TODO Auto-generated method stub
		boolean flag = false;
		String sqlc = "SELECT COUNT(bat_id) FROM battery";
		this.pstmt = this.conn.prepareStatement(sqlc);
		ResultSet rs = this.pstmt.executeQuery();
		int batNum = 0;
		while(rs.next()){
			batNum = rs.getInt(1)+1;
		}
		this.pstmt.close();
		String sql = "INSERT INTO Battery(bat_id,bat_num,rate_voltage,work_voltage,rate_volume,weight,pro_time,bat_type,bat_station,manu_id,bat_version)VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,bat.getBatID());
		this.pstmt.setInt(2,batNum);
		this.pstmt.setString(3,bat.getRateVoltage());
		this.pstmt.setString(4,bat.getWorkVoltage());
		this.pstmt.setInt(5,bat.getRateVolume());
		this.pstmt.setInt(6,bat.getWeight());
		this.pstmt.setDate(7,new java.sql.Date(bat.getProTime().getTime()));
		this.pstmt.setString(8,bat.getBatType());
		this.pstmt.setString(9,bat.getBatStation());
		this.pstmt.setString(10,bat.getManuID());
		this.pstmt.setString(11,bat.getBatVersion());
		if(this.pstmt.executeUpdate()>0){
			flag = true;
		}
		this.pstmt.close();
		return flag;
	}
	@Override
	public List<Battery> findBySta(String sta) throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Battery> all = new ArrayList<Battery>();
		String sql = "SELECT bat_id,bat_num,rate_voltage,work_voltage,rate_volume,weight,pro_time,bat_type,bat_station,bat_version" +
				" FROM Battery WHERE bat_station=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,sta);
		ResultSet rs = this.pstmt.executeQuery();
		if(rs.next()){
			Battery bat = new Battery();
			bat.setBatID(rs.getString(1));
			bat.setBatNum(rs.getInt(2));
			bat.setRateVoltage(rs.getString(3));
			bat.setWorkVoltage(rs.getString(4));
			bat.setRateVolume(rs.getInt(5));
			bat.setWeight(rs.getInt(6));
			bat.setProTime(rs.getDate(7));
			bat.setBatType(rs.getString(8));
			bat.setBatStation(rs.getString(9));
			bat.setBatVersion(rs.getString(10));
			all.add(bat);
		}
		this.pstmt.close();
		return all;
	}
	@Override
	public List<Battery> findByVer(String manuID, String version)
			throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Battery> all = new ArrayList<Battery>();
		String sql = "SELECT bat_id,bat_num,rate_voltage,work_voltage,rate_volume,weight,pro_time,bat_type,bat_station,bat_version" +
				" FROM Battery WHERE manu_id=? AND bat_version=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,manuID);
		this.pstmt.setString(2,version);
		ResultSet rs = this.pstmt.executeQuery();
		if(rs.next()){
			Battery bat = new Battery();
			bat.setBatID(rs.getString(1));
			bat.setBatNum(rs.getInt(2));
			bat.setRateVoltage(rs.getString(3));
			bat.setWorkVoltage(rs.getString(4));
			bat.setRateVolume(rs.getInt(5));
			bat.setWeight(rs.getInt(6));
			bat.setProTime(rs.getDate(7));
			bat.setBatType(rs.getString(8));
			bat.setBatStation(rs.getString(9));
			bat.setBatVersion(rs.getString(10));
			all.add(bat);
		}
		this.pstmt.close();
		return all;
	}
	@Override
	public List<Battery> findByBat(String type, String rateVoltage,
			int rateVolume) throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Battery> all = new ArrayList<Battery>();
		String sql = "SELECT bat_id,bat_num,rate_voltage,work_voltage,rate_volume,weight,pro_time,bat_type,bat_station,bat_version" +
				" FROM Battery WHERE bat_type=? AND work_voltage=? AND rate_volume=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,type);
		this.pstmt.setString(2,rateVoltage);
		this.pstmt.setInt(3,rateVolume);
		ResultSet rs = this.pstmt.executeQuery();
		if(rs.next()){
			Battery bat = new Battery();
			bat.setBatID(rs.getString(1));
			bat.setBatNum(rs.getInt(2));
			bat.setRateVoltage(rs.getString(3));
			bat.setWorkVoltage(rs.getString(4));
			bat.setRateVolume(rs.getInt(5));
			bat.setWeight(rs.getInt(6));
			bat.setProTime(rs.getDate(7));
			bat.setBatType(rs.getString(8));
			bat.setBatStation(rs.getString(9));
			bat.setBatVersion(rs.getString(10));
			all.add(bat);
		}
		this.pstmt.close();
		return all;
	}
	@Override
	public List<Battery> findBySiName(String siName) throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Battery> all = new ArrayList<Battery>();
		String sql = "SELECT bat.bat_id,bat.bat_num,bat.rate_voltage,bat.work_voltage,bat.rate_volume,bat.weight,bat.pro_time,bat.bat_type,bat.bat_station,bat.bat_version FROM stockin si JOIN batin bi ON si.si_id = bi.si_id " +
				"JOIN battery bat ON bi.bat_id = bat.bat_id WHERE si.sier_name = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1,siName);
		ResultSet rs = this.pstmt.executeQuery();
		while(rs.next()){
			Battery bat = new Battery();
			bat.setBatID(rs.getString(1));
			bat.setBatNum(rs.getInt(2));
			bat.setRateVoltage(rs.getString(3));
			bat.setWorkVoltage(rs.getString(4));
			bat.setRateVolume(rs.getInt(5));
			bat.setWeight(rs.getInt(6));
			bat.setProTime(rs.getDate(7));
			bat.setBatType(rs.getString(8));
			bat.setBatStation(rs.getString(9));
			bat.setBatVersion(rs.getString(10));
			all.add(bat);
		}
		this.pstmt.close();
		return all;
	}
	
	

}
